/**
   :: History of the development of BBA, a Web SQL interface ::

2005-10-05	BBA v3.00
			* Changed "Save to File" button to "Export result to Excel"
			# Fixed bba.jsp for whitespace use pre mode and jscript error of incorrect form element name.

2005-01-07	BBA v2.96
			* Added dbcon.close()
			+ Added warning message in bba.jsp
			+ Batch transaction support with auto commit.

2004-09-09	BBA v2.95
			* Port to J2EE application by Napoleon

2004-07-09	BBA v2.95
			* Fixed the unstable PRE mode by using JavaScript to set the style at runtime.

2004-07-08	BBA v2.94
			+ Add option for showing real name of primary key constraint.

2004-07-07	BBA v2.93
			+ Added "reset" command to reset the cached Connection.
			+ Added "Time Elapsed" statistic information.
			# Compatible for both SilverStream35 and SilverStream37.

2004-06-07	BBA v2.92
			# Supports UTF-16LE, UTF-16BE and UTF-8 encoding's input file.

2004-05-21	BBA v2.91
			* Minor fix for spaced special commands. The parsed per-SQL statement is trimmed().

2004-05-10	BBA v2.9
			* Fixed MS SQL Server "Connection is busying with another result of hstmt" error after update -> query in one batch.
			# bba.html retrieve version number from boquerytable on page load.
			# Reconstructed boquerytable and renamed to boBBA.

2004-05-06	BBA v2.82
			# Removed status textarea, use lb_out for output.
			# Able to retrieve dbcc's output by using statement.getWarnings().
			# Use server session to store the separated Connection for restoring it after the static dbcon is refreshed to null.

2004-04-27	BBA v2.81
			# Use the same business object boquerytable for both query and update.
			# Case insensited internal command bba.dump.
			# Trim SQL token.
			# Automatic lower case of sp_[^ ]*.
			- Removed printStackTrace() when exception arised in either query or update in bba.html

2004-03-25	BBA v2.8 (kernel changed after testing on 187/intgrp)
			# For the compatibility of older Oracle server the following modifications are done :
			  boquerytable
			  	- Change back to use executeQuery() instead of execute()
			  OracleTableStructureDumper
			  	- getColumnTypeName()
			  	  Obtain all table structure information from table all_tab_columns instead of calling JDBC methods.

2004-03-23	BBA v2.8
			# Refactorized VUtil.sql.TableStructureDumper. Now it is a bridge only.
			# Have accurate Table Structure dumping of table column name, type, precision information plus primary key constraint.
			: Tested bba.dump with three different database servers : Adaptive Server Enterprise, Microsoft SQL Server and Oracle.
			+ VUtil.sql :
				ITableStructureDumper - Interface class being declared in TableStructureDumper.
				ATableStructureDumper - Base class that implements ITableStructureDumper that works for Sybase ASE.
				MSSQLTableStructureDumper - A child of ATableStructureDumper that works specially for Microsoft SQL Server.
				OracleTableStructureDumper - A child of ATableStructureDumper that works specially for Oracle.
			+ bba_to.xml - For SilverStream37's silvercmd publishfromfile command.
			+ filelist.dtd - used by bba_to.xml.
			+ publish_to.bat - A batch file that used command extension of the cmd shell to prompt parameters for where to publish bba to.

2004-03-22	BBA v2.7
			# Fallback to use SilverStream's connection when cannot get a separate connection.
			+ VUtil.sql.TableStructureDumper
			  It's a class for dumping table structure. Tested on Sybase ASE-12.
			+ Added bba.dump special keyword for dumping table structure. It can be used in the same way as any general SQL statement.
			* Fixed some style sheet problems.
			# Added "Special commands" section under the selective checkboxes.
			# Updated bba.xml for new entry VUtil.sql.TableStructureDumper.

2004-03-05 	BBA v2.6
			+ Reuse the same connection globally thus saving connection and shorten response time.
			+ Synchronized a global object to avoid reuse of interrupted connection.
			+ Support T-SQL(such as the use of cursor) better since the connection is kept over sessions.

 */
package com.erp.app.common;

import java.awt.*;
import java.util.*;
import java.math.*;

import javax.servlet.http.*;
import java.io.*;
import java.net.*;
import java.sql.*;
import java.lang.reflect.*;

import com.erp.framework.model.dao.ServiceLocator;
import com.erp.app.common.VUtil.sql.TableStructureDumper;


public class BBA
{
	boolean g_bSuppressQuotes = false, g_bIgnoreError = false, g_bDumpInsert = false, g_bShowRealName = false, g_bSingleStmt = false;
	protected final static int g_majorVersion = 3, g_minorVersion = 0;
	protected final static String g_dateVersion = "2005-10-05";

	java.sql.Connection dbcon = null;
	java.sql.Statement stmt = null;
	ResultSet rs = null;
	String dbName, g_encoding, g_dbcon_session, sql = "";
	boolean bIsOracle, bIsMS;
	int ir = 0;
	long dtStart, dtLast, dtNow;

	StringBuffer ret_str = new StringBuffer(32768);

	public Object invoke(String query, boolean isUpdate)
		throws Exception
	{

		Object ret = null;

		ir = 0;
		iStart = 0;
		iEnd = 0;
		ret_str = new StringBuffer(32768);
		//		sess = ((AgoServer)evt.getServer()).getHttpServer().getSession();
		//		g_dbcon_session = evt.getDatabase().getName() + ".bba.dbcon";
		g_encoding = new OutputStreamWriter(System.out).getEncoding();

		try {
			dbcon = ServiceLocator.getInstance().getDSDBConnection();
			dbcon.setAutoCommit(false);

			dbName = dbcon.getMetaData().getDatabaseProductName();
			bIsOracle = "Oracle".equals(dbName);
			bIsMS = "Microsoft SQL Server".equals(dbName);

			ret = handleSQL(query, isUpdate);
			dbcon.commit();

		} catch (Exception e) {
			dbcon.rollback();
			e.printStackTrace();
			int i = sql.length() > 2048 ? 2048 : sql.length();
			ByteArrayOutputStream bout = new ByteArrayOutputStream();
			PrintStream out = new PrintStream(bout);
			e.printStackTrace(out);
			if ( e instanceof SQLWarning ) {
				ret = bout.toString() + "\n" + e;
			}
			else {
				ret = bout.toString() + "\n" + new Exception("@" + ir + " : " + e.getMessage() + "\n" + sql.substring(0, i));
			}
		} finally {
			if ( null != dbcon ) {
				dbcon.close();
			}
//			dbcon = null;
//			System.gc();
		}

		return ret;
	}


	int iStart = 0, iEnd = 0;
	public void getNextSQL(String query) {
		if ( ir > 0 )
			iStart = iEnd + 3;
		iEnd = query.indexOf(";\r\n", iStart);
		if ( g_bSingleStmt ) {
			iEnd = -1;
		}
		if ( -1 == iEnd ) {
			iEnd = query.length();
			if ( !g_bSingleStmt && iEnd > 0 && query.charAt(--iEnd) != ';' )
				iEnd++;
		}
		if ( iStart < query.length() ) {
			sql = query.substring(iStart, iEnd).trim();
			ir++;
		}
	}

	private String preFormatSQL(String sql) {
		if ( sql.length() >= 4 ) {
			String s = sql.substring(0,4).toLowerCase();
			sql = replace(sql.trim(), "\r", " ");
			if ( s.startsWith("sp_") ||  s.startsWith("bba." ) ) {
				int spc = sql.indexOf(" ");
				if ( -1 == spc )
					spc = sql.length();
				sql = sql.substring(0, spc).toLowerCase() + sql.substring(spc);
			}
		}
		return sql;
	}

	private void handleQueryException(Statement stmt, SQLException x) throws SQLException, SQLWarning {
		try {
			if ( !g_bIgnoreError && x.getMessage().indexOf("JZ0R2") == -1 ) {
				SQLWarning sw = stmt.getWarnings();
				StringBuffer sb = new StringBuffer();
				while ( null != sw ) {
					sb.append(sw.getMessage());
					sw = sw.getNextWarning();
				}
				if ( sb.length() > 0 ) {
					x.setNextException(new SQLWarning(sb.toString()));
				}
				throw x;
			}
		} finally {
			try {
				stmt.close();
			} catch ( SQLException sqlx ) {
				sqlx.printStackTrace();
			}
		}
	}

	String sTable = "", sColumns[];
	private int buildHeaderColumn(ArrayList rows, ResultSet rs) throws Exception {
		ResultSetMetaData metaData = rs.getMetaData();
		int numberOfColumns =  metaData.getColumnCount();
		sColumns = new String[numberOfColumns];
		if ( numberOfColumns > 0 ) {
			sColumns[0] = metaData.getColumnName(1);
			for ( int i = 1; i < numberOfColumns; i++ )
				sColumns[i] = metaData.getColumnName(i + 1);
			rows.add(sColumns);
			if ( g_bDumpInsert )
				sTable = getTableName(sql);
		}
		return numberOfColumns;
	}

	private void checkForInterrupt() throws Exception {
		dtNow = new java.util.Date().getTime();
		if ( dtNow - dtLast > 1000 ) {
//							System.out.println("read = " + in.read());
//							PushbackInputStream in = new PushbackInputStream(request.getInputStream());
//							System.out.println("client = " + client + " output test " + in.available());
			File file = new File(System.getProperty("java.io.tmpdir") + "bba.stop");
			if ( file.exists() && file.lastModified() > dtStart ) {
				dbcon = null;
				System.gc(); // Kill the connection instantly.
				throw new Exception("This bba is started since " + new java.util.Date(dtStart) + ". bba.stop is found with date " + new java.util.Date(file.lastModified()));
			}
			dtLast = dtNow;
		}
	}

	private void processResultSet(ResultSet rs, ArrayList rows, int numberOfColumns) throws Exception {
		boolean isXPCmd = 0 == sql.indexOf("xp_cmdshell");
		Object o;
		while ( rs.next() )  {
			ArrayList listColumn = new ArrayList(), listRez = new ArrayList(), listInsertRez = new ArrayList();
			for ( int i = 0; i < numberOfColumns; i++ ) {
				if ( isXPCmd ) {
					String s = rs.getString(i + 1);
					o = s.getBytes(g_encoding);
				}
				else {
					try {
						o = rs.getObject(i + 1);
					} catch ( Exception x ) {
						o = "";
					}
				}
			 	String s = getQuotedString(o);
				if ( null != o ) {
					listColumn.add(sColumns[i]);
					listInsertRez.add(s);
				}
				listRez.add(s);
			}
			if ( g_bDumpInsert ) {
				ret_str.append("\nINSERT INTO " + sTable + " ( " + concateString(listColumn.toArray(), ",") + " ) VALUES ( " + concateString(listInsertRez.toArray(), ",") + " );");
			}
			// Check for flag file for at least 5 seconds interval
			checkForInterrupt();
			rows.add(listRez.toArray());
		}
		if ( g_bDumpInsert ) {
			ret_str.append("\n\n");
		}
		rs.close();
	}

	private void helpText(ArrayList rows, AbstractList rez) {
		StringBuffer sb = new StringBuffer();
		Object objs[] = rows.toArray();
		rows.clear();
		int k = 0;
		for ( int j = 0; j < objs.length; j++, k++ ) {
			Object objs2[] = (Object[])objs[j];
			if ( objs2.length > 0 ) {
				if ( 0 == k )
					rows.add(new Object[] { objs2[0] });
				else
					sb.append((String)objs2[0]);
			} else {
				rows.add(new Object[] { sb.toString() });
				rows.add(new String[0]);
				sb = new StringBuffer();
				k = -1;
			}
		}
		rows.add(new Object[] { sb.toString() });
		rez.add(rows.toArray());
	}

	private Object handleSQL(String query, boolean bUpdate) throws Exception {

		int nUpdate = 0;

		Vector rez = new Vector();
		ArrayList rows = new ArrayList();
		dtStart = new java.util.Date().getTime();

		DatabaseMetaData dmd = dbcon.getMetaData();
		dtLast = new java.util.Date().getTime();
		getNextSQL(query);
		ArrayList vp_rows = new ArrayList();
		do {
			boolean bSkip = false;
			if ( isNothing(sql) )
				bSkip = true;
			else
				sql = preFormatSQL(sql);
			stmt = dbcon.createStatement();
			if ( 0 == sql.indexOf("reset") ) {
				dbcon = null;
				stmt = null;
				System.gc();
				bSkip = true;
			} else if ( 0 == sql.indexOf("bba.dump") ) {
				TableStructureDumper tsd = new TableStructureDumper(dbcon);
				if ( g_bShowRealName ) {
					tsd.setShowRealPK(true);
				}
				String s = tsd.getTableStructure(sql.substring("bba.dump".length() + 1, sql.length()));
				if ( g_bDumpInsert )
					ret_str.append(s);
				else {
					appendStrings(s, rez);
				}
				bSkip = true;
				tsd = null;
			}
			else {
				try {
					if ( bUpdate ) {
						nUpdate += stmt.executeUpdate(sql);
						bSkip = true;
					} else {
						if ( bIsMS )
							rs = stmt.executeQuery(sql); // MS SQL Server supports this way to get ResultSet only. A call to stmt.getResultSet() would cause an exception.
						else
							stmt.executeQuery(sql);
					}
				} catch ( SQLException x ) {
					bSkip = true;
					System.out.println("Executing Query: <"+sql+">: "+x.getMessage());
					handleQueryException(stmt, x);
				}
			}

			if ( !bSkip ) {
				int nRows = 0;
				rows.clear();
				do {
					if ( null == rs )				// For the compatibility of MS SQL Server
						rs = stmt.getResultSet();	//
					if ( rows.size() > 0 ) {
						rows.add(new String[0]);
						rows.add(new String[0]);
					}
					if ( null != rs ) {
						nRows = rows.size();
						processResultSet(rs, rows, buildHeaderColumn(rows, rs));
						nRows = rows.size() - nRows - 1;
					}

					SQLWarning sw = stmt.getWarnings();
					StringBuffer sb = new StringBuffer();
					while ( null != sw ) {
						sb.append(sw.getMessage());
						sw = sw.getNextWarning();
					}
					if ( sb.length() > 0 ) {
						rows.add(new String[] { sb.toString() });
					}
					rs = null;
				} while ( stmt.getMoreResults() );

				rez.add(new Object[] { new Object[] { "Total rows" }, new Object[] { new Integer(nRows) } });

				if ( null != sql ) {
					if ( sql.indexOf("sp_helptext") == 0 ) {
						helpText(rows, rez);
					} else if ( sql.indexOf("vp_update_item_code") == 0 ) {
						vp_rows.add(rows.toArray());
					} else {
						rez.add(rows.toArray());
					}
				}
				else
					rez.add(rows.toArray());
			}
			getNextSQL(query);
		} while ( iStart < query.length() );

		// Is vp_update_item_code ?
		if ( vp_rows.size() > 0 ) {
			StringBuffer sb = new StringBuffer();
			for ( int i = 0; i < vp_rows.size(); i++ ) {
				Object[] o = (Object[])vp_rows.get(i);
				for ( int j = 0; j < o.length; j++ ) {
					if ( o[j] instanceof Object[] ) {
						Object[] row = (Object[])o[j];
						for ( int k = 0; k < row.length; k++ ) {
							sb.append(row[k]);
						}
					}
				}
			}
			rez.add(new Object[] { new String[] { sb.toString() } });
		}
		rez.insertElementAt(new Object[] { new Object[] { "Time Elapsed" }, new Object[] { (new java.util.Date().getTime() - dtStart) + "ms" } }, 0);

		if ( bUpdate )
			return "[ " + new Timestamp(new java.util.Date().getTime()) + " ] - ok! " + nUpdate + " row" + (nUpdate > 1 ? "s" : "") + " updated!";
		else {
			if ( g_bDumpInsert )
				return ret_str.toString();
			else
				return rez.toArray();
		}
	}

	private void appendStrings(String s, AbstractList list) {
		StringTokenizer st = new StringTokenizer(s, "\n");
		ArrayList lines = new ArrayList();
		lines.add(s);
		list.add(new Object[] { lines.toArray() });
	}

	private String getQuotedString(Object o) {
		if ( null == o )
			return "null";
		if ( o instanceof String || o instanceof Timestamp )
			return escaqe(o.toString());
		if ( o instanceof byte[] ) {
			// Try through known encoding methods to have higher chance to get the right encoding for the string
			String encs[] = {
				"utf-8", "big5", "gb2312"
			};
			byte[] b = (byte[]) o;

			String s;
			try {
				for ( int i = 0; i < encs.length; i++ ) {
					s = new String(b, encs[i]);
//					if ( s.getBytes(encs[i]).length == b.length )
						return escaqe(s);
				}
			} catch ( UnsupportedEncodingException e ) {
			}
			return "";
		}
		return o.toString();
	}
	private String getString(Object o) {
		if ( null == o )
			return "null";
		return o.toString();
	}

	private String getTableName(String s) {
		StringTokenizer st = new StringTokenizer(s);
		while ( st.hasMoreTokens() )
			if ( st.nextToken().toLowerCase().equals("from") ) {
				s = st.nextToken();
				if ( st.countTokens() > 0 ) {
					String sTemp = st.nextToken();
					if ( !"where".equals(sTemp.toLowerCase()) )
						s = sTemp;
				}
				return s;
			}
		return "";
	}

	public boolean isNothing(Object o) {
		if ( null != o ) {
			if ( o instanceof String )
				return "".equals(((String)o).trim());
		}
		else
			return true;
		return false;
	}

	public String concateString(Object[] objs, String sMiddle) {
		if ( null == objs || objs.length < 1 )
			return "";
		String s = getString(objs[0]);
		for ( int i = 1; i < objs.length; i++ )
			s += sMiddle + getString(objs[i]);
		return s;
	}

	public String escaqe(String s) {
		if ( null == s )
			return "null";
		if ( g_bSuppressQuotes )
			return s;
		return "'" + escape(s) + "'";
	}

	public String escape(String s) {
		if ( null == s )
			return "null";
		return escape(s, '\'');
	}

	public String escape(String s, char c) {
		return replace(s, String.valueOf(c), String.valueOf(c) + String.valueOf(c));
	}

	public String replace(String s, String oldStr, String newStr) {
		StringBuffer sb = new StringBuffer();
		int i = 0, lasti = 0, nOldStr = oldStr.length();
		while ( -1 != (i = s.indexOf(oldStr, i)) ) {
			sb.append(s.substring(lasti, i));
			sb.append(newStr);
			i += nOldStr;
			lasti = i;
		}
		sb.append(s.substring(lasti, s.length()));
		return sb.toString();
	}

	public static String getVersion() {
		return g_majorVersion + "." + g_minorVersion;
	}

	public static String getVersionDate() {
		return g_dateVersion;
	}

	public void setIsDumpInsert(boolean isDumpInsert) {
		g_bDumpInsert = isDumpInsert;
	}

	public void setIsSuppressQuotes(boolean isSuppressQuotes) {
		g_bSuppressQuotes = isSuppressQuotes;
	}

	public void setIsIgnoreError(boolean isIgnoreError) {
		g_bIgnoreError = isIgnoreError;
	}

	public void setIsShowRealName(boolean isShowRealName) {
		g_bShowRealName = isShowRealName;
	}
	public void setIsSingleStmt(boolean isSingleStmt) {
		g_bSingleStmt = isSingleStmt;
	}

	public void debug(String s)  {
		System.out.println(new java.util.Date() + " " + s);
	}


}
